package com.huaxia.dao.realAct;

import com.huaxia.pojo.realAct.BranchBmAct;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/08 16:25
 */

public interface BranchBmActMapper {

    @Select("select x.BRANCHSNAME,x.branchBmAdvance5000,x.branchBmAdvance7000,x.branchBmAdvance10000,y.branchBmInSure5000,y.branchBmInSure7000,y.branchBmInSure10000 from          \n" +
            "(select sb.branchsname,NVL(a.a,0)branchBmAdvance5000,NVL(a.b,0)branchBmAdvance7000,NVL(a.c,0)branchBmAdvance10000 from           \n" +
            "(select x.BRANCHCODE,SUM(case when x.mys>='5000' then 1 end)a,SUM(case when x.mys>='7000' then 1 end)b,SUM(case when x.mys>='10000' then 1 end)c from            \n" +
            "(select t.branchcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where \n" +
            "TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) AND t.RNFLAG='N' and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职' AND t.AGENTGRADE in('BM','SBM') group by t.agentcode,t.branchcode)x group by x.BRANCHCODE)a \n" +
            "right join SD_BRANCH sb on sb.BRANCHCODE=a.BRANCHCODE)x, \n" +
            "\n" +
            "(select sb.branchsname,NVL(a.a,0)branchBmInSure5000,NVL(a.b,0)branchBmInSure7000,NVL(a.c,0)branchBmInSure10000 from  \n" +
            "(select x.BRANCHCODE,SUM(case when x.YCB>='5000' then 1 end)a,SUM(case when x.YCB>='7000' then 1 end)b,SUM(case when x.YCB>='10000' then 1 end)c FROM\n" +
            "( select a.BRANCHCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "            (select t.BRANCHCODE, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "            where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职'  AND t.AGENTGRADE in('BM','SBM')\n" +
            "            group by t.AGENTCODE,t.BRANCHCODE)a left join \n" +
            "            (select s.BRANCHCODE, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "            where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职'  AND s.AGENTGRADE in('BM','SBM')\n" +
            "            group by s.AGENTCODE,s.BRANCHCODE)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.BRANCHCODE)a right join SD_BRANCH sb on sb.BRANCHCODE=a.BRANCHCODE)y \n" +
            "where x.BRANCHSNAME=y.BRANCHSNAME order by y.branchBmInSure10000 desc")
    List<BranchBmAct> getBranchBmAct();
}
